package com.tskj.leonsu.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.tskj.leonsu.db.DbUtilitys;
import com.tskj.leonsu.utility.ModuleType;
import com.tskj.leonsu.utility.Tools;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Component
public class BathRoomDAO {

    private final String MODULE_TYPE = String.valueOf(ModuleType.XI_YU.ordinal());

    @Autowired
    private DbUtilitys dbUtilitys;

    public JSONArray getBathRoomList(String sex) {
        String sql = "select bid id, bname name from bathroom where sex=? and used=1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, sex);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public String getBathRoomId(String bathRoomName) {
        String sql = "select bid from bathroom where bname=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, bathRoomName);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("bid");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public JSONArray getTimeAndCountList(String bathId, String date) {
        String sql = "select b_reserve_time.tid,b_reserve_time.starttime, b_reserve_time.endtime " +
                ", bathroom.bname, (b_reserve_time.bnum - ifnull(a.count, 0)) bnum  from b_reserve_time " +
                "left join bathroom on bathroom.bid=? " +
                "left join (select starttime, bname, count(*) as count from bath_order where startdate=? and orderstate='0' group by bname, starttime) a on cast(a.starttime as char(8)) = cast(b_reserve_time.starttime as char(8)) and a.bname=bathroom.bname " +
                "where b_reserve_time.bid=? and b_reserve_time.used=1 order by starttime";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, bathId, date, bathId);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public String getBathSortNo(String id, String name) {
        String sql = "select sortnum from bathroom where bid=? and bname=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, id, name);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getTimeSortNo(String bathId, String timeId) {
        String sql = "select sortnum from b_reserve_time where bid=? and tid=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, bathId, timeId);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getMaxOrderNo(String bathName, String startDate, String startTime, String endTime) {
        String sql = "select (ifnull(max(sortnum), 0) + 1) sortnum from bath_order where bname=? and starttime=? and endtime=? and startdate=? for update";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, bathName, startTime, endTime, startDate);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    /**
     * 检查此浴室的此日期的此时间段是否已订满
     *
     * @param hairRoom
     * @param startTime
     * @param endTime
     * @param status
     * @param startDate
     * @return
     */
    public boolean canSave(String hairRoom, String startTime, String endTime, int status, String startDate) {
        String sql = "select (b_reserve_time.bnum - ifnull(a.count, 0)) emptycount from b_reserve_time left join " +
                "(select count(*) count, bname,starttime,endtime from bath_order where startdate=? and bname=? and starttime=? and endtime=? and orderstate=0 for update" +
                ") a on a.starttime = b_reserve_time.starttime and a.endtime=b_reserve_time.endtime " +
                "right join bathroom on bathroom.bid=b_reserve_time.bid and bathroom.bname=? " +
                "where b_reserve_time.starttime=? and b_reserve_time.endtime=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, startDate, hairRoom, startTime, endTime, hairRoom, startTime, endTime);
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("emptycount");
        if (value == null || "".equals(value.toString()) || 0 >= Integer.valueOf(value.toString())) {
            return false;
        }
        return true;
    }

    public void save(String id, String studentName, String sex, String xueYuan, int maxOrderNo, String bathRoom, String startTime, String endTime, int status, String code, String startDate, String adUserName) throws Exception {
        String sql = "insert into bath_order (orderid, userid, sex, xueYuan, sortnum, bname, starttime, endtime, orderstate, qrcode, startdate, adusername, createdate) " +
                "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now())";
        dbUtilitys.execSQLWithTran(sql, id, studentName, sex, xueYuan, maxOrderNo, bathRoom, startTime, endTime, status, code, startDate, adUserName);
    }

    public JSONObject getStartTime() {
        JSONObject ret;
        String sql = "select starttime as start, endtime as end from modules where id=0";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql);
        if (list == null || list.size() == 0) {
            ret = new JSONObject();
            ret.put("start", "08:00:00");
            ret.put("end", "23:59:59");
            return ret;
        }
        return JSONObject.parseObject(JSONObject.toJSONString(list.get(0)));
    }

    public boolean hasOrder(String adUserName, String date) {
        int orderCount = getOrderByAdUserName(adUserName, date);
        return orderCount >= 1;
    }

    public int getOrderByAdUserName(String adUserName, String date) {
        String sql = "select ifnull(count(*), 0) count from bath_order where adusername=? and startdate=? and orderstate='0' for update";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, date);
        if (list == null || list.size() == 0) {
            return 0;
        }
        return Integer.valueOf(list.get(0).get("count").toString());
    }

    /**
     * 获取未完成订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getUnFinishedOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate, " +
                "date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now() canCancel, " +
                "str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now() canConfirm " +
                "from bath_order " +
                "where adusername=? and orderstate=0 " +
                "union " +
                "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate, " +
                "date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now() canCancel, " +
                "str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now() canConfirm " +
                "from bath_order_history " +
                "where createdate>=DATE_SUB(date(now()), INTERVAL 5 day) and adusername=? and orderstate=0 " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 获取已完成订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getFinishedOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate from bath_order " +
                "where adusername=? and orderstate=1 " +
                "union " +
                "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate from bath_order_history " +
                "where createdate>=DATE_SUB(date(now()), INTERVAL 5 day) and adusername=? and orderstate=1 " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 获取全部订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getAllOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate from bath_order " +
                "where adusername=? " +
                "union " +
                "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate from bath_order_history " +
                "where createdate>=DATE_SUB(date(now()), INTERVAL 5 day) and adusername=? " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    private boolean exists(String orderId) {
        String sql;
        List<Map<String, Object>> list;
        sql = "select count(*) as col from bath_order where orderid=?";
        list = dbUtilitys.execSQL(sql, orderId);
        if (list == null || Integer.valueOf(list.get(0).get("col").toString()) == 0) {
            return false;
        }
        return true;
    }

    /**
     * 获取订单是否允许确认
     *
     * @param orderId
     * @return 0：不能确认，1：可以确认
     */
    public Boolean getCanConfirm(String orderId) {
        String tableName;
        if (!exists(orderId)) {
            tableName = "bath_order_history";
        } else {
            tableName = "bath_order";
        }

        String sql = "select str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now() canConfirm from " + tableName + " where orderid=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, orderId);
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("canConfirm");
        if (value == null || !"1".equals(value.toString())) {
            return false;
        }
        return true;

    }

    public void confirmOrder(String adUserName, String orderId) throws Exception {
        String tableName;
        if (!exists(orderId)) {
            tableName = "bath_order_history";
        } else {
            tableName = "bath_order";
        }
        String sql = "update " + tableName + " set orderstate=1, ctime=now() where orderid=? and adusername=? and str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now()";
        dbUtilitys.execSQLWithTran(sql, orderId, adUserName);
    }

    /**
     * 获取订单是否允许取消
     *
     * @param orderId
     * @return false：不能取消，true：可以取消
     */
    public boolean getCanCancel(String orderId) {
        String tableName;
        if (!exists(orderId)) {
            tableName = "bath_order_history";
        } else {
            tableName = "bath_order";
        }

        String sql = "select date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now() canCancel from " + tableName + " where orderid=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, orderId);
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("canCancel");
        if (value == null || !"1".equals(value.toString())) {
            return false;
        }
        return true;
    }

    public void cancelOrder(String adUserName, String orderId) throws Exception {
        String tableName;
        if (!exists(orderId)) {
            tableName = "bath_order_history";
        } else {
            tableName = "bath_order";
        }

        String sql = "update " + tableName + " set orderstate=2, ctime=now() where orderid=? and adusername=? and date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now()";
        dbUtilitys.execSQLWithTran(sql, orderId, adUserName);
    }

}
